package mobilebind

import (
	"context"
	"database/sql"
	"fmt"
	"github.com/zeromicro/go-zero/core/stores/cache"
	"github.com/zeromicro/go-zero/core/stores/sqlx"
	"strconv"
	"strings"
)

var _ YunhuMobileBindModel = (*customYunhuMobileBindModel)(nil)

type (
	// YunhuMobileBindModel is an interface to be customized, add more methods here,
	// and implement the added methods in customYunhuMobileBindModel.
	YunhuMobileBindModel interface {
		yunhuMobileBindModel
		FindListByPhoneid(ctx context.Context, phoneIds int64) ([]*YunhuMobileBind, error)
		RestoryBatch(ctx context.Context, phoneIds []int64) error
		InsertBatch(ctx context.Context, dataList []*YunhuMobileBind) (sql.Result, error)
		ListByPhoneid(ctx context.Context, phoneIds []int64) ([]*YunhuMobileBind, error)
		ListByids(ctx context.Context, ids []int64) ([]*YunhuMobileBind, error)
		List(ctx context.Context, customerId int64, accountId int64, phoneId int64, page int64, pagesize int64) ([]*YunhuMobileBind, error)
		Count(ctx context.Context, customerId int64, accountId int64, phoneId int64) (int64, error)
		AccountBindList(ctx context.Context, customerId int64, accountId int64, phone string, page int64, pagesize int64) ([]*YunhuMobileBindAccount, error)
		AccountBindCount(ctx context.Context, customerId int64, accountId int64, phoneId string) (int64, error)
	}

	Counts struct {
		Total int64 `db:"total"`
	}

	YunhuMobileBindAccount struct {
		Id         int64  `db:"id"`
		AccountId  int64  `db:"account_id"`  // 座席ID
		PhoneId    int64  `db:"phone_id"`    // 云号ID
		CreateTime int64  `db:"create_time"` // 绑定事件
		Phone      string `db:"phone"`       // 号码
		Iccid      string `db:"iccid"`       // 号码iccid
		CityName   string `db:"city_name"`   // 归属城市名称
		Formates   int64  `db:"formates"`    // 制式:1=移动,2=联通,3=电信,4=广电

	}

	customYunhuMobileBindModel struct {
		*defaultYunhuMobileBindModel
	}
)

func (m *customYunhuMobileBindModel) AccountBindList(ctx context.Context, customerId int64, accountId int64, phone string, page int64, pagesize int64) ([]*YunhuMobileBindAccount, error) {
	var sqlStr = m.accountBindWhere(customerId, accountId, phone)
	var resp []*YunhuMobileBindAccount
	query := fmt.Sprintf("SELECT b.id,b.account_id,b.phone_id,b.create_time,m.phone,m.iccid,m.city_name,m.formates from yunhu_mobile_bind as b LEFT JOIN yunhu_mobile as m on b.phone_id = m.id %s ORDER BY b.id DESC limit ?,?", sqlStr)
	if err := m.QueryRowsNoCacheCtx(ctx, &resp, query, (page-1)*pagesize, pagesize); err != nil && err != ErrNotFound {
		return nil, err
	}

	return resp, nil
}

func (m *customYunhuMobileBindModel) AccountBindCount(ctx context.Context, customerId int64, accountId int64, phone string) (int64, error) {
	var sqlWheres string = m.accountBindWhere(customerId, accountId, phone)
	var countTotal Counts
	var sql string = fmt.Sprintf("SELECT COUNT(b.id) as total  from yunhu_mobile_bind as b LEFT JOIN yunhu_mobile as m on b.phone_id = m.id %s", sqlWheres)
	var err error = m.QueryRowNoCacheCtx(ctx, &countTotal, sql)
	return countTotal.Total, err
}

func (m *customYunhuMobileBindModel) accountBindWhere(customerId int64, accountId int64, phone string) string {
	var sqlWhere []string = make([]string, 0)

	if customerId > 0 {
		sqlWhere = append(sqlWhere, fmt.Sprintf(" b.customer_id = %d ", customerId))
	}

	if accountId > 0 {
		sqlWhere = append(sqlWhere, fmt.Sprintf(" b.account_id = %d ", accountId))
	}

	if phone != "" {
		sqlWhere = append(sqlWhere, fmt.Sprintf(" m.phone = '%s' ", phone))
	}

	var sqlStr = ""
	if len(sqlWhere) > 0 {
		sqlStr = strings.Join(sqlWhere, " and ")
		if sqlStr != "" {
			sqlStr = " where " + sqlStr
		}
	}

	return sqlStr
}

func (m *customYunhuMobileBindModel) FindListByPhoneid(ctx context.Context, phoneIds int64) ([]*YunhuMobileBind, error) {
	var resp []*YunhuMobileBind
	query := fmt.Sprintf("select %s from %s where phone_id  = ? order by id desc", yunhuMobileBindRows, m.table)
	if err := m.QueryRowsNoCacheCtx(ctx, &resp, query, phoneIds); err != nil && err != ErrNotFound {
		return nil, err
	}

	return resp, nil
}

func (m *customYunhuMobileBindModel) InsertBatch(ctx context.Context, dataList []*YunhuMobileBind) (sql.Result, error) {
	var values []string = make([]string, len(dataList))
	for key, data := range dataList {
		values[key] = fmt.Sprintf("(%d, %d, %d, '%s', %d)", data.CustomerId, data.AccountId, data.PhoneId, data.BindId, data.CreateTime)
	}
	query := fmt.Sprintf("insert into %s (%s) values %s ", m.table, yunhuMobileBindRowsExpectAutoSet, strings.Join(values, ","))
	return m.ExecNoCacheCtx(ctx, query)
}

// 批量销户
func (m *customYunhuMobileBindModel) RestoryBatch(ctx context.Context, phoneIds []int64) error {
	phoneList, errs := m.ListByPhoneid(ctx, phoneIds)
	if errs != nil {
		return errs
	}

	if len(phoneList) == 0 {
		return nil
	}

	var cacheKey []string = make([]string, 0)
	var ids []string = make([]string, 0)
	for _, val := range phoneList {
		ids = append(ids, strconv.Itoa(int(val.Id)))
		cacheKey = append(cacheKey, fmt.Sprintf("%s%s", cacheYunhuMobileBindIdPrefix, val.Id))
		cacheKey = append(cacheKey, fmt.Sprintf("%s%v:%v", cacheYunhuMobileBindCustomerIdAccountIdPrefix, val.CustomerId, val.AccountId))
	}

	_, err := m.ExecCtx(ctx, func(ctx context.Context, conn sqlx.SqlConn) (result sql.Result, err error) {
		query := fmt.Sprintf("delete from %s where `id` in(%s)", m.table, strings.Join(ids, ","))
		return conn.ExecCtx(ctx, query)
	}, cacheKey...)

	return err
}

// 查询ID信息
func (m *customYunhuMobileBindModel) ListByPhoneid(ctx context.Context, phoneIds []int64) ([]*YunhuMobileBind, error) {
	var resp []*YunhuMobileBind
	query := fmt.Sprintf("select %s from %s where phone_id in(%s) order by id desc", yunhuMobileBindRows, m.table, m.IntArrToString(phoneIds))
	if err := m.QueryRowsNoCacheCtx(ctx, &resp, query); err != nil && err != ErrNotFound {
		return nil, err
	}

	return resp, nil
}

// 查询ID信息
func (m *customYunhuMobileBindModel) ListByids(ctx context.Context, ids []int64) ([]*YunhuMobileBind, error) {
	var resp []*YunhuMobileBind
	query := fmt.Sprintf("select %s from %s where id in(%s) order by id desc", yunhuMobileBindRows, m.table, m.IntArrToString(ids))
	if err := m.QueryRowsNoCacheCtx(ctx, &resp, query); err != nil && err != ErrNotFound {
		return nil, err
	}

	return resp, nil
}

func (m *customYunhuMobileBindModel) List(ctx context.Context, customerId int64, accountId int64, phoneId int64, page int64, pagesize int64) ([]*YunhuMobileBind, error) {
	var sqlWheres string = m.getWhere(customerId, accountId, phoneId)
	var resp []*YunhuMobileBind
	query := fmt.Sprintf("select %s from %s %s order by id desc limit ?,?", yunhuMobileBindRows, m.table, sqlWheres)
	if err := m.QueryRowsNoCacheCtx(ctx, &resp, query, (page-1)*pagesize, pagesize); err != nil && err != ErrNotFound {
		return nil, err
	}

	return resp, nil
}

func (m *customYunhuMobileBindModel) Count(ctx context.Context, customerId int64, accountId int64, phoneId int64) (int64, error) {
	var sqlWheres string = m.getWhere(customerId, accountId, phoneId)
	var countTotal Counts
	var sql string = fmt.Sprintf("select count(*) as total from %s %s", m.table, sqlWheres)
	var err error = m.QueryRowNoCacheCtx(ctx, &countTotal, sql)
	return countTotal.Total, err
}

func (m *customYunhuMobileBindModel) getWhere(customerId int64, accountId int64, phoneId int64) string {
	var sqlWhere []string = make([]string, 0)

	if customerId > 0 {
		sqlWhere = append(sqlWhere, fmt.Sprintf(" customer_id = %d ", customerId))
	}

	if accountId > 0 {
		sqlWhere = append(sqlWhere, fmt.Sprintf(" account_id = %d ", accountId))
	}

	if phoneId > 0 {
		sqlWhere = append(sqlWhere, fmt.Sprintf(" phone_id = %d ", phoneId))
	}

	if len(sqlWhere) == 0 {
		return ""
	}

	var sqlStr string = strings.Join(sqlWhere, " and ")
	if sqlStr != "" {
		sqlStr = " where " + sqlStr
	}

	return sqlStr
}

func (m *customYunhuMobileBindModel) IntArrToString(idsInt []int64) string {
	var idsString []string = m.IntArrToStringArr(idsInt)
	return strings.Join(idsString, ",")
}

func (m *customYunhuMobileBindModel) IntArrToStringArr(idsInt []int64) []string {
	var ids []string = make([]string, len(idsInt))
	for ke, val := range idsInt {
		ids[ke] = fmt.Sprintf("%d", val)
	}

	return ids
}

// NewYunhuMobileBindModel returns a model for the database table.
func NewYunhuMobileBindModel(conn sqlx.SqlConn, c cache.CacheConf, opts ...cache.Option) YunhuMobileBindModel {
	return &customYunhuMobileBindModel{
		defaultYunhuMobileBindModel: newYunhuMobileBindModel(conn, c, opts...),
	}
}
